package DAO;


import java.sql.*;
import java.util.ArrayList;

import javax.swing.JOptionPane;

import Entity.Contact;
import Entity.Item;


public class ControlDB {
	Connection con;
	PreparedStatement ps;
	Statement st;
	ResultSet rs;
	boolean addAND=false;

	public ControlDB() {
	}

	public void getConnection() {
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
			con = DriverManager.getConnection(
					"jdbc:sqlserver://localhost:1130;database=MockProject;"+ "user=sa;"+ "password=12345678");
		} catch (ClassNotFoundException e) {
			JOptionPane.showMessageDialog(null, e.getMessage(),
					"Driver Not Found", JOptionPane.ERROR_MESSAGE);
			System.exit(1);

		} catch (SQLException e) {
			JOptionPane.showMessageDialog(null, e.getMessage(),
					"Database Error", JOptionPane.ERROR_MESSAGE);
			System.exit(1);

		}
	}	
	
	public void addContact( Contact contact){
		try{
			getConnection();
			String sql= "insert into Contact values ("
						+"'"+contact.getFirstName()+"',"
						+"'"+contact.getSurName()  +"',"
					    +"'"+contact.getKnownAs()  +"',"
						+"'"+contact.getOfficePhone()+"',"
					    +"'"+contact.getMobilePhone()+"',"
					    +"'"+contact.getStHomePhone()+"',"
					    +"'"+contact.getEmailAddress()+"',"
					    +"'"+contact.getManagerName()+"',"
					    +"'"+contact.getContactType()+"',"
					    +"'"+contact.getBestContactMethod()+"',"
					    +"'"+contact.getJobRole() +"',"
					    +"'"+contact.getWorkBase()+"',"
					    +"'"+contact.getLobTitle()+"',"
					    +"'"+contact.getIsActive()+"')";
			st=con.createStatement();
			 st.executeUpdate(sql);
		}catch(SQLException e){
			JOptionPane.showMessageDialog(null, e.getMessage(),
   					"Database Error", JOptionPane.ERROR_MESSAGE);
		}
		
			finally {
	            if (con != null) {
	               try {
	                  con.close();
	               } catch (Exception e) {
	            	   JOptionPane.showMessageDialog(null, e.getMessage(),
	       					"Database Error", JOptionPane.ERROR_MESSAGE);
	               }
	            }
	         }
		
	}
	
	public int totalFields(String firstname, String surname,boolean isactive){
		int count=0;
		try {
			getConnection();
			String sql="SELECT * "+
                    "FROM Contact a WHERE a.isactive = 'true' AND ";
			if(firstname !=null && firstname.length()>0){
				sql+="a.firstname LIKE '%"+firstname+"%' ";
				addAND=true;
				}
				if(surname !=null && surname.length()>0){
				if(addAND){
				sql+=" AND a.surname LIKE '%"+surname+"%' ";
				}
				else {
				sql+=" a.surname LIKE '%"+surname+"%' ";
				}
				}
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				count++;
			}
			count=count/5+1;
			
			return count;
		} catch (SQLException e) {
			 JOptionPane.showMessageDialog(null, e.getMessage(),
    					"Database Error", JOptionPane.ERROR_MESSAGE);

		}
		finally {
            if (con != null) {
               try {
                  con.close();
               } catch (Exception e) {
                    e.printStackTrace();
               }
            }
         }
       
		return count;
	}
	
	public int totalFields(String firstname, String surname){
		int count=0;
		try {
			getConnection();
			String sql="SELECT * "+
                    "FROM Contact a WHERE  ";
			if(firstname !=null && firstname.length()>0){
				sql+="a.firstname LIKE '%"+firstname+"%' ";
				addAND=true;
				}
				if(surname !=null && surname.length()>0){
				if(addAND){
				sql+=" AND a.surname LIKE '%"+surname+"%' ";
				}
				else {
				sql+=" a.surname LIKE '%"+surname+"%' ";
				}
				}
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				count++;
			}
			count=count/5+1;
			return count;
		} catch (SQLException e) {
			 JOptionPane.showMessageDialog(null, e.getMessage(),
    					"Database Error", JOptionPane.ERROR_MESSAGE);

		}
		finally {
            if (con != null) {
               try {
                  con.close();
               } catch (Exception e) {
                    e.printStackTrace();
               }
            }
         }
       
		return count;
	}
	
	public ArrayList<String> getType(){
		ArrayList<String> listType=new ArrayList<String>();
		try {
			getConnection();
			String sql="SELECT DISTINCT contacttype "+
                    "FROM Contact ";
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				listType.add( new String(rs.getString("contacttype")));
			}
			return listType;
		} catch (SQLException e) {
			 JOptionPane.showMessageDialog(null, e.getMessage(),
    					"Database Error", JOptionPane.ERROR_MESSAGE);

		}
		finally {
            if (con != null) {
               try {
                  con.close();
               } catch (Exception e) {
                    e.printStackTrace();
               }
            }
         }
       
                return null;
	}
	
	public ArrayList<String> getBest(){
		ArrayList<String> listBest=new ArrayList<String>();
		try {
			getConnection();
			String sql="SELECT DISTINCT bestcontactmethod "+
                    "FROM Contact ";
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				listBest.add( new String(rs.getString("bestcontactmethod")));
			}
			return listBest;
		} catch (SQLException e) {
			 JOptionPane.showMessageDialog(null, e.getMessage(),
    					"Database Error", JOptionPane.ERROR_MESSAGE);

		}
		finally {
            if (con != null) {
               try {
                  con.close();
               } catch (Exception e) {
                    e.printStackTrace();
               }
            }
         }
       
                return null;
	}
	
	public ArrayList<Item> getContactType() {
		ArrayList<Item> listContactType=new ArrayList<Item>();
		try {
			getConnection();
			String sql="SELECT * "+
                    "FROM Contact ";
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				listContactType.add( new Item(rs.getString("contacttype"),rs.getString("bestcontactmethod")));
			}
			return listContactType;
		} catch (SQLException e) {
			 JOptionPane.showMessageDialog(null, e.getMessage(),
    					"Database Error", JOptionPane.ERROR_MESSAGE);

		}
		finally {
            if (con != null) {
               try {
                  con.close();
               } catch (Exception e) {
                    e.printStackTrace();
               }
            }
         }
       
                return null;
	}
	
	public ArrayList<Contact> getSearchName(String surname,String firstname,int pageNum,int pageSize) {
		ArrayList<Contact> listContact=new ArrayList<Contact>();
		boolean addAND=false;
        int rowStart=(pageNum-1)*pageSize+1;
        int rowEnd=rowStart+pageSize-1;
		
		try {
			getConnection();
			String sql="SELECT * "+
                    "FROM (" +
                    "SELECT *,ROW_NUMBER() OVER (ORDER BY id) AS RowNum "
                    +" FROM Contact a "
                    +"WHERE ";

if(firstname !=null && firstname.length()>0){
sql+="a.firstname LIKE '%"+firstname+"%' ";
addAND=true;
}
if(surname !=null && surname.length()>0){
if(addAND){
sql+=" AND a.surname LIKE '%"+surname+"%' ";
}
else {
sql+=" a.surname LIKE '%"+surname+"%' ";
}
}
sql+=") AS TOB "
        +" WHERE TOB.RowNum BETWEEN "+rowStart+" AND "+rowEnd+" ";
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				listContact.add( new Contact(rs.getInt("id"),rs.getString("firstname"),rs.getString("surname"),
						rs.getString("knownas"),rs.getInt("officephone"),rs.getInt("mobilephone"),
						rs.getInt("sthomephone"),rs.getString("emailaddress"),rs.getString("managername"),
						rs.getString("contacttype"),rs.getString("bestcontactmethod"),rs.getString("jobrole"),
						rs.getString("workbase"),rs.getString("lobtitle"),
						rs.getBoolean("isactive")));
			
				
			}
			return listContact;
		} catch (SQLException e) {
			 JOptionPane.showMessageDialog(null, e.getMessage(),
    					"Database Error", JOptionPane.ERROR_MESSAGE);

		}
		finally {
            if (con != null) {
               try {
                  con.close();
               } catch (Exception e) {
                    e.printStackTrace();
               }
            }
         }
       
                return null;
	}
	
	public ArrayList<Contact> getSearchName(String surname,String firstname,int pageNum,int pageSize,boolean isactive) {
		ArrayList<Contact> listContact=new ArrayList<Contact>();
		boolean addAND=false;
        int rowStart=(pageNum-1)*pageSize+1;
        int rowEnd=rowStart+pageSize-1;
		
		try {
			getConnection();
			String sql="SELECT * "+
                    "FROM (" +
                    "SELECT *,ROW_NUMBER() OVER (ORDER BY id) AS RowNum "
                    +" FROM Contact a "
                    +"WHERE a.isactive = 'true' AND ";

if(firstname !=null && firstname.length()>0){
sql+="a.firstname LIKE '%"+firstname+"%' ";
addAND=true;
}
if(surname !=null && surname.length()>0){
if(addAND){
sql+=" AND a.surname LIKE '%"+surname+"%' ";
}
else {
sql+=" a.surname LIKE '%"+surname+"%' ";
}
}
sql+=") AS TOB "
        +" WHERE TOB.RowNum BETWEEN "+rowStart+" AND "+rowEnd+" ";

			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				listContact.add( new Contact(rs.getInt("id"),rs.getString("firstname"),rs.getString("surname"),
						rs.getString("knownas"),rs.getInt("officephone"),rs.getInt("mobilephone"),
						rs.getInt("sthomephone"),rs.getString("emailaddress"),rs.getString("managername"),
						rs.getString("contacttype"),rs.getString("bestcontactmethod"),rs.getString("jobrole"),
						rs.getString("workbase"),rs.getString("lobtitle"),
						rs.getBoolean("isactive")));
				
				
			}
			return listContact;
		} catch (SQLException e) {
			 JOptionPane.showMessageDialog(null, e.getMessage(),
    					"Database Error", JOptionPane.ERROR_MESSAGE);

		}
		finally {
            if (con != null) {
               try {
                  con.close();
               } catch (Exception e) {
                    e.printStackTrace();
               }
            }
         }
       
                return null;
	}
}
